"""
获取问财选股数据:首板涨停非ST,放量,筹码集中
"""
from datetime import datetime
import time

import pandas as pd
from DrissionPage import ChromiumPage
from DrissionPage import WebPage
from DrissionPage._units.listener import DataPacket
from DrissionPage.errors import ElementNotFoundError

from x01_stock.xx_util.DBUtil import SqlHelper


def handle_data_by_pandas(data):
    # 使用pandas的DataFrame来存储数据
    # 设置默认表头
    # columns = [col[0] for col in cursor.description]
    # 手动设置表头
    columns = ['id', '交易日期', '股票代码', '股票名称', '股价', '今日涨幅', '集中度90排名','A股流通市值','买入信号','技术形态','90成本下限','90%成本上限','收盘获利','平均成本','集中度70%','集中度90%', '放量',
              '连续涨停天数','近三日涨幅','近5日涨幅','近10日涨幅','近20日涨幅','近一月涨幅','近三月涨幅','近一年涨幅','年初至今涨幅','竞价量(股)','竞价金额','换手率','主力资金流向','个股热度','dde散户数量','所属概念']
    df = pd.DataFrame(data, columns=columns)
    df['股价'] = pd.to_numeric(df['股价'], errors='coerce')
    df['今日涨幅'] = pd.to_numeric(df['今日涨幅'], errors='coerce')
    df['A股流通市值'] = (pd.to_numeric(df['A股流通市值'], errors='coerce') / 10000).round(2)
    df['90成本下限'] = pd.to_numeric(df['90成本下限'], errors='coerce')
    df['90%成本上限'] = pd.to_numeric(df['90%成本上限'], errors='coerce')
    df['收盘获利'] =  pd.to_numeric(df['收盘获利'], errors='coerce')
    df['平均成本'] =  pd.to_numeric(df['平均成本'], errors='coerce')
    df['集中度70%'] =  pd.to_numeric(df['集中度70%'], errors='coerce')
    df['集中度90%'] = pd.to_numeric(df['集中度90%'], errors='coerce')
    df['放量'] =  pd.to_numeric(df['放量'], errors='coerce')
    df['竞价量(股)'] = pd.to_numeric(df['竞价量(股)'], errors='coerce')
    df['竞价金额'] = pd.to_numeric(df['竞价金额'], errors='coerce')
    df['主力资金流向'] = pd.to_numeric(df['主力资金流向'], errors='coerce')
    df['个股热度'] = pd.to_numeric(df['个股热度'], errors='coerce')
    df['dde散户数量'] = pd.to_numeric(df['dde散户数量'], errors='coerce')
    print('数据查询已完成')
    return df


def export_to_excel(data, excel_path):
    """
    首板涨停的数据导出到Excel文件
    """
    print(f"Data exported to {excel_path}")
    data.to_excel(excel_path, index=False)


def get_first_zhangting(stat_date):
    # 开启一个浏览器
    page = ChromiumPage()
    # page.listen.start('https://www.iwencai.com/customized/chart/get-robot-data')unifiedwap/result?w=%E9%A6%96%E6%9D%BF%E6%B6%A8%E5%81%9C%E9%9D%9Est&querytype=stock&addSign=1721951853969&sign=1722552059734

    page.listen.start('https://www.iwencai.com/customized/chart/get-robot-data')

    # page.get('https://www.iwencai.com/unifiedwap/result?w=%E9%A6%96%E6%9D%BF%E6%B6%A8%E5%81%9C%E9%9D%9Est%E9%9D%9E%E6%96%B0%E8%82%A1%EF%BC%8C&querytype=stock&addSign=1722600131697')
    page.get(
        'https://www.iwencai.com/unifiedwap/result?w=%E9%A6%96%E6%9D%BF%E6%B6%A8%E5%81%9C%E9%9D%9EST,%E6%94%BE%E9%87%8F,%E7%AD%B9%E7%A0%81%E9%9B%86%E4%B8%AD&querytype=stock')
    time.sleep(3)
    try:  # header = res.request.postData
        res = page.listen.wait()  # 等待并获取一个数据包
        data1 = res.response.body['data']['answer'][0]['txt'][0]['content']['components'][0]['data']['datas']

        # 插入问财返回的基本信息
        for data in data1:
            # print(data)
            sql = (
                f"INSERT INTO `z_sproot_series`.`stock_wencai_first_zhangting` (trade_date,chengbenshangxian90pct,chengbenxiaxiang90pct,aguliutongshizhi,"
                f"mairuxinhao,pingjunchengben,jishuxingtai,shoupanhuoli,fangliang,stock_price,chg_pct,stock_code ,stock_name,lianxuzhangtingtianshu,"
                f"jizhongdu70pct,jizhongdu90pct,jzd90_paiming) values ('{stat_date}',")
            # 字段进行排序，保证插入的顺序正确
            data = dict(sorted(data.items(), key=lambda item: item[0]))  #创建一个新的字典，该字典包含与原始字典data相同的键值对，但这些键值对会根据键的升序排序。
            # print(data)

            for key, value in data.items():
                # print(key + ':' + str(value))
                if key != 'market_code' and key != 'code':
                    # print(type(value))
                    if type(value) == str:
                        sql = sql + '"' + str(value) + '",'
                    else:
                        sql = sql + str(value) + ','
            sql = sql[:-1] + ');'
            # print('--------------------------------')
            # print(sql)
            SqlHelper.get_instance().execute_curd(sql)

        # 【第二个查询】
        print('点击获取表现的数据')
        page.ele('@text()=表现').click()
        time.sleep(3)
        res2 = page.listen.wait()
        data2 = res2.response.body['data']['answer'][0]['txt'][0]['content']['components'][0]['data']['datas']
        # print(data2)
        for data in data2:
            data = dict(sorted(data.items(), key=lambda item: item[0]))
            print(data)
            stock_code = ''
            sql = (
                'update stock_wencai_first_zhangting set nianchuzhijinzhangfu = %s,jin10rizhangfu = %s, jin20rizhangfu = %s , jin3rizhangfu = %s ,jin5rizhangfu = %s ,'
                ' jin1yuerizhangfu = %s,jin1nianzhangfu = %s,jin3yuezhangfu = %s  ')
            set_value = []
            for key in data.keys():
                if key == 'code' or key == 'market_code' or key == '最新价' or key == '最新涨跌幅' or key == '股票简称':
                    continue
                elif key == '股票代码':
                    stock_code = data.get(key)
                else:
                    if type(data[key]) == float or type(data[key]) == int:
                        set_value.append(key + ':' + str(round(data.get(key), 2)))
                    else:
                        set_value.append(key + ':' + data.get(key))
            print(set_value)
            sql = sql + ' where stock_code = "' + stock_code + '" and trade_date = "' + stat_date + '";'
            print(sql)
            SqlHelper.get_instance().execute_curd(sql, set_value)

        # 【 获取技术的数据】
        page.ele('@text()=技术').click()
        time.sleep(3)
        res3 = page.listen.wait()
        data3 = res3.response.body['data']['answer'][0]['txt'][0]['content']['components'][0]['data']['datas']
        print(data3)

        for data in data3:
            data = dict(sorted(data.items(), key=lambda item: item[0]))
            print(data)
            stock_code = ''
            sql = 'update stock_wencai_first_zhangting set ddesanhu = %s ,geguredu = %s ,zhulizijin = %s ,suoshugainian = %s, huanshoulv = %s ,jingjialiang = %s ,jingjiajin_e = %s '
            set_value = []
            for key in data.keys():
                if key == 'code' or key == 'market_code' or key == '最新价' or key == '最新涨跌幅' or key == '股票简称' or key.startswith(
                        '技术形态') or key.startswith('集中度90'):
                    continue
                elif key == '股票代码':
                    stock_code = data.get(key)
                else:
                    set_value.append(data.get(key))
            print(set_value)
            sql = sql + ' where stock_code = "' + stock_code + '" and trade_date = "' + stat_date + '";'
            print(sql)
            SqlHelper.get_instance().execute_curd(sql, set_value)


    except ElementNotFoundError:
        print('程序异常退出')
    # page.quit()
    # 打印响应的JSON数据[0]


if __name__ == '__main__':
    # 获取指定日期的数据
    # stat_date = datetime(2024, 8, 6).date().strftime('%Y-%m-%d')
    # get_first_zhangting(stat_date)

    stat_date = datetime.now().date().strftime('%Y-%m-%d')
    # 获取当天涨停的数据
    get_first_zhangting(stat_date)
    # 数据处理完成，生成需要的股票融资融券数据
    output_file_path = 'd:\\stock_research\\' + stat_date + '_首板涨停.xlsx'
    query = (f'SELECT `id`, `trade_date`, `stock_code`, `stock_name`, `stock_price`, `chg_pct`, `jzd90_paiming`, `aguliutongshizhi`,'
             f' `mairuxinhao`, `jishuxingtai`, `chengbenxiaxiang90pct`, `chengbenshangxian90pct`, `shoupanhuoli`, `pingjunchengben`, '
             f'`jizhongdu70pct`, `jizhongdu90pct`,`fangliang`, `lianxuzhangtingtianshu`, `jin3rizhangfu`, `jin5rizhangfu`, `jin10rizhangfu`, '
             f'`jin20rizhangfu`, `jin1yuerizhangfu`, `jin3yuezhangfu`,`jin1nianzhangfu`, `nianchuzhijinzhangfu`, `jingjialiang`, `jingjiajin_e`, '
             f'`huanshoulv`, `zhulizijin`, `geguredu`, `ddesanhu`, `suoshugainian` '
             f' from stock_wencai_first_zhangting where trade_date ="{stat_date}" order by stock_code ')
    records = SqlHelper.get_instance().execute_query(query)
    data = handle_data_by_pandas(records)
    if data is not None:
        print('导出数据到excel文件')
        export_to_excel(data, output_file_path)